﻿using CrystalDecisions.Web;
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Resources;

namespace PMPUtils.Report
{
    public class DependentLevelOperator
    {
        /// <summary>
        ///  Refresh the overall dependent level data from local DB.
        /// </summary>
        /// <param name="reportSource"></param>
        public static void RefreshOverallCrystalReport(CrystalReportSource reportSource)
        {
            String strQueryOverall = "select Resource, cast(SUM(CAST(dephours as decimal(18, 2)))/SUM(totalhours) as decimal(18, 2)) Level, sum(totalhours) 'Total Hours', sum(dephours) 'Dependent Hours'" +
                   " from DependentLevel" +
                   " where Revision = 'CURRENT'" +
                   " group by Resource" +
                   " order by cast(SUM(CAST(dephours as decimal(18, 2)))/SUM(totalhours) as decimal(18, 2))  desc";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtOverall = dbOperator.Read(strQueryOverall);

            reportSource.ReportDocument.SetDataSource(dtOverall);
            reportSource.ReportDocument.SetDatabaseLogon(DBConfigurations.LOCAL_SA, DBConfigurations.LOCAL_PASSWORD);

        }

        /// <summary>
        /// Refresh the report based on monthly data
        /// </summary>
        /// <param name="reportSource"></param>
        public static void RefreshMonthlyCrystalReport(CrystalReportSource reportSource)
        {
            String strQueryMonthly = "select Resource Assignee, yearmonth Month, cast(SUM(CAST(dephours as decimal(18, 2)))/SUM(totalhours) as decimal(18, 2)) 'Detailed-Level', sum(totalhours) 'Total Hours', sum(dephours) 'Dependent Hours'" +
                   " from DependentLevel" +
                   " where Revision = 'CURRENT'" +
                   " group by Resource, yearmonth" +
                   " order by cast(SUM(CAST(dephours as decimal(18, 2)))/SUM(totalhours) as decimal(18, 2)) desc";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtMonthly = dbOperator.Read(strQueryMonthly);

            reportSource.ReportDocument.SetDataSource(dtMonthly);
            reportSource.ReportDocument.SetDatabaseLogon(DBConfigurations.LOCAL_SA, DBConfigurations.LOCAL_PASSWORD);
        }

        /// <summary>
        /// Sync the depdendent level with JIRA database
        /// </summary>
        public static void RefreshDependentLevels()
        {
            String[] resources = ResourceOperator.ReadResources("'NF1', 'NF2', 'OPTM'");

            RemoveAllDependentLevels();

            // Create the cache table
            DataTable dependentLevels = new DataTable(); // New data table.
            dependentLevels.Columns.Add("Resource", typeof(string)); // Add five columns.
            dependentLevels.Columns.Add("Task", typeof(string));
            dependentLevels.Columns.Add("YearMonth", typeof(string));
            dependentLevels.Columns.Add("TotalHours", typeof(decimal));
            dependentLevels.Columns.Add("DepHours", typeof(decimal));

            for (int j = 0; j < resources.Length; j++)
            {
                String JIRATimeLogging = "select jiraissue.assignee 'Resource'," +
                    "       jiraissue.pkey 'Task'," +
                    "       CONVERT(varchar, year(worklog.created)) + CONVERT(varchar, Right(100+Month(worklog.created),2)) as 'YearMonth'," +
                    "       cast(sum(worklog.timeworked/3600) as decimal(18, 2)) 'TotalHours'," +
                    "       cast(sum(case when author <> '" + resources[j].ToString() + "' then worklog.timeworked/3600 else 0.00 end) as decimal(18, 2)) 'DepHours'" +
                    " from worklog join jiraissue" +
                    "   on jiraissue.id = worklog.issueid" +
                    "   where worklog.created >= '2011/07/01' and worklog.created <= '2012/06/30'" +
                    " and jiraissue.assignee in ('" + resources[j].ToString() + "')" +
                    " group by jiraissue.assignee, jiraissue.pkey, CONVERT(varchar, year(worklog.created)) + CONVERT(varchar, Right(100+Month(worklog.created),2))";

                DBOperator JIRAOperator = new DBOperator(DBConfigurations.JIRA_SQL_DB_CONNECTION);
                DataTable dt = JIRAOperator.Read(JIRATimeLogging);
                dependentLevels.Merge(dt);
            }

            foreach (DataRow row in dependentLevels.Rows)
            {
                String name = row["resource"].ToString();
                String task = row["task"].ToString();
                String yearMonth = row["yearMonth"].ToString();
                double totalHours = Convert.ToDouble(row["totalHours"]);
                double depHours = Convert.ToDouble(row["DepHours"]);

                String insertCommandText = "insert into DependentLevel (resource, task, yearmonth, totalhours, dephours, createddate, revision)" +
                    "values ('" + name + "','" + task + "','" + yearMonth + "'," + totalHours + "," + depHours + ",'" + DateTime.Now.ToString("MM/dd/yyyy") + "', 'CURRENT')";
                DBOperator localOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                localOperator.Update(insertCommandText);
            }
        }

        /// <summary>
        /// Remove all dependent levels
        /// </summary>
        public static void RemoveAllDependentLevels()
        {
            String strRemoveAll = "delete from DependentLevel";
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strRemoveAll);
        }
    }
}
